<?php
class stYoutubeToolboxdashboard_model extends stmodel_2_1{

    public function __construct($config) {
        parent::__construct ( $config );
    }

    public function getTotalView(){
        $stYoutubeToolboxstatistics_model = $this->loadmodel("stYoutubeToolboxstatistics");
        $stYoutubeToolboxvideo_model = $this->loadmodel("stYoutubeToolboxvideo");
        $args = array();
        $user_id = get_current_user_id();
        $table_user = $this->prefix.'st_youtube_user';
        $args['custom_query'] = "
        SELECT sum(view) view FROM {$stYoutubeToolboxstatistics_model->_tableName} a
        INNER JOIN $table_user user on user.id = a.user_id
        WHERE user.user_id = $user_id";
        $results = $stYoutubeToolboxvideo_model->getRecords($args);
        $result = $results[0];
        return $totalView = $result->view;
    }

    public function getMostViewVideo(){
        $stYoutubeToolboxstatistics_model = $this->loadmodel("stYoutubeToolboxstatistics");
        $stYoutubeToolboxvideo_model = $this->loadmodel("stYoutubeToolboxvideo");
        
        $args = array();
        $user_id = get_current_user_id();
        $table_user = $this->prefix.'st_youtube_user';
        $args['custom_query'] = "
        SELECT * FROM {$stYoutubeToolboxstatistics_model->_tableName} a
        INNER JOIN $table_user user on user.id = a.user_id
        WHERE user.user_id = $user_id AND view = (SELECT max(view) FROM {$stYoutubeToolboxstatistics_model->_tableName})
        ";
        $results = $stYoutubeToolboxstatistics_model->getRecords($args);
        $result = $results[0];
        $mostVideoId = $result->video_id;

        $args['custom_query'] = "SELECT * FROM {$stYoutubeToolboxvideo_model->_tableName}
        WHERE video_id like('$mostVideoId')
        ";
        $results = $stYoutubeToolboxvideo_model->getRecords($args);
        return $mostVideo = $results[0];
    }

    public function getMostCommentVideo(){
        $stYoutubeToolboxvideo_model = $this->loadmodel("stYoutubeToolboxvideo");
        $stYoutubeToolboxcomment_model = $this->loadmodel("stYoutubeToolboxcomment");

        $args = array();
        $user_id = get_current_user_id();
        $table_user = $this->prefix.'st_youtube_user';
        $args['custom_query'] = "
            SELECT * FROM (
            SELECT count(video_id) numvideo, video_id, user_id
            FROM {$stYoutubeToolboxcomment_model->_tableName} group by video_id) table1
            INNER JOIN $table_user user on user.id = table1.user_id
            WHERE user.user_id = $user_id AND numvideo = (
            SELECT max(numvideo)
            FROM (
            SELECT count(video_id) numvideo, video_id
            FROM {$stYoutubeToolboxcomment_model->_tableName} group by video_id) table1)
        ";
        $results = $stYoutubeToolboxcomment_model->getRecords($args);
        $result = $results[0];
        $mostCommentVideoId = $result->video_id;

        $args['custom_query'] = "SELECT * FROM {$stYoutubeToolboxvideo_model->_tableName}
        WHERE video_id like('$mostCommentVideoId')
        ";
        $results = $stYoutubeToolboxvideo_model->getRecords($args);
        return $mostCommentVideo = $results[0];
    }

    public function getViewYesterdayAndYesterBefore(){
        $stYoutubeToolboxstatistics_model = $this->loadmodel("stYoutubeToolboxstatistics");
        $toDay = date('j');
        $month = date('n');
        $year = date('Y');
        $yesterday = $toDay - 1;
        $yesterdaybefore = $yesterday - 1;

        $args = array();
        $args['fields'] = array('sum(statistic.view) view');
        $args['where'] = " DAY(statistic.cdate)=$yesterday AND MONTH(statistic.cdate) = $month AND YEAR(statistic.cdate) = $year";
        $args['group_by'] = ' DAY(statistic.cdate) ';
        ////////////
        $user_id = get_current_user_id();
        $args['where'] .= " AND user.user_id = $user_id";
        ///////////////
        $results = $stYoutubeToolboxstatistics_model->getRecords($args);
        $yesterdayResult = $results[0];
        $yesterdayView = $yesterdayResult->view;

        $args = array();
        $args['fields'] = array('sum(statistic.view) view');
        $args['where'] = " DAY(statistic.cdate)=$yesterdaybefore AND MONTH(statistic.cdate) = $month AND YEAR(statistic.cdate) = $year";
        $args['group_by'] = ' DAY(statistic.cdate) ';
        ////////////
        $user_id = get_current_user_id();
        $args['where'] .= " AND user.user_id = $user_id";
        ///////////////
        $results = $stYoutubeToolboxstatistics_model->getRecords($args);
        $yesterdaybeforeResult = $results[0];
        $yesterdaybeforeView = $yesterdaybeforeResult->view;

        $viewYesterdayPlusYesterdaybefore = $yesterdayView - $yesterdaybeforeView;
        return $viewYesterdayPlusYesterdaybefore;
    }
}